﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Glossary
{
    public static class IODatabase_ReadWriteFromWordLibrary
    {
        /// <summary>
        /// Ansvar: Kim Nielsen
        /// </summary>
        /// <param name="word"></param>
        public static void WriteNewWordToDataSource(Word word)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["Group7Connection"].ConnectionString;

            using (SqlConnection sqlConnection1 = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();

                cmd.CommandText = string.Format("AddWordsProc '{0}', '{1}', '{2}', '{3}'", word.ForeignLanguage, word.ForeignWord, word.SwedishWord, word.SwedishDescription);
                cmd.CommandType = CommandType.Text;
                cmd.Connection = sqlConnection1;

                sqlConnection1.Open();

                int count = cmd.ExecuteNonQuery();

            }
        }
        /// <summary>
        /// Ansvar: Kim Nielsen
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="correctedTest"></param>
        public static void WriteCreatedTestToDataSource(CreatedTest createdTest)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["Group7Connection"].ConnectionString;

            using (SqlConnection sqlConnection1 = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();

                cmd.CommandText = string.Format("INSERT INTO CreatedTests(DateOfCreation,CourseID,NbrOfQuestions) VALUES ('{0}','{1}','{2}');SELECT @@Identity",
                                        createdTest.DateOfCreation, createdTest.CourseID, createdTest.NumberOfWords);
                cmd.CommandType = CommandType.Text;
                cmd.Connection = sqlConnection1;
                sqlConnection1.Open();
                int createdTestID = 0;
                int.TryParse(cmd.ExecuteScalar().ToString(), out createdTestID);

                sqlConnection1.Close();

                foreach (Word word in createdTest.Words)
                {
                    cmd.CommandText = string.Format("INSERT INTO TestWords(CreatedTestID, WordID) VALUES('{0}', '{1}')", createdTestID, word.WordID);
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = sqlConnection1;
                    sqlConnection1.Open();
                    cmd.ExecuteNonQuery();
                    sqlConnection1.Close();
                }



            }
        }
        /// <summary>
        /// Ansvar: Kim Nielsen
        /// ReadWordsFromDB läser in ord från databas med språk som inparameter och lägger till dem i WordLibrary
        /// </summary>
        /// <param name="wordLibrary"></param>
        /// <param name="fileName"></param>
        public static void ReadGlossaryFromDataSource(ref WordLibrary wordLibrary, string lang)
        {
            Language language = (Language)Enum.Parse(typeof(Language), lang);
            Word word;
            string connectionString = ConfigurationManager.ConnectionStrings["Group7Connection"].ConnectionString;

            using (SqlConnection sqlConnection1 = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                SqlDataReader reader;

                cmd.CommandText = string.Format("EXEC ReadWordLibraryProc '{0}'", lang);
                cmd.CommandType = CommandType.Text;
                cmd.Connection = sqlConnection1;

                sqlConnection1.Open();

                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    word = new Word(int.Parse(reader[0].ToString()), reader[2].ToString().Trim(), reader[1].ToString().Trim(), reader[4].ToString().Trim(), language);
                    wordLibrary.AddWord(word);
                }

            }
        }

        public static List<string> ReadCoursesFromDataSource()
        {
            string connectionString = ConfigurationManager.ConnectionStrings["Group7Connection"].ConnectionString;

            using (SqlConnection sqlConnection1 = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                SqlDataReader reader;

                cmd.CommandText = string.Format("SELECT CourseID FROM Courses");
                cmd.CommandType = CommandType.Text;
                cmd.Connection = sqlConnection1;

                sqlConnection1.Open();
                reader = cmd.ExecuteReader();

                List<string> courses = new List<string>();
                int i = 0;
                while (reader.Read())
                {
                    courses.Add(reader[0].ToString().Trim());
                    i++;
                }

                return courses;
            }
        }

        /// <summary>
        /// Ansvar: Kim Nielsen
        /// Hämtar ut info om språk från fil
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        //public static Language LanguageOfFile(string fileName)
        //{
        //    //using (StreamReader sr = new StreamReader(fileName, Encoding.Unicode))
        //    //{
        //    //    string line = sr.ReadLine();
        //    //    return (Language)Enum.Parse(typeof(Language), line);
        //    //}
        //}

        /// <summary>
        /// Ansvar: Kim Nielsen
        /// Skriver WordLibrary till fil
        /// </summary>
        /// <param name="language"></param>
        /// <param name="filePath"></param>
        //public static void WriteGlossaryToFile(List<Word> wordList, Language language, string fileName)
        //{
        //    //using (StreamWriter sw = new StreamWriter(fileName, false, Encoding.Unicode))
        //    //{
        //    //    sw.WriteLine(language.ToString());

        //    //    foreach (Word word in wordList)
        //    //    {
        //    //        sw.WriteLine(word);
        //    //    }
        //    //}
        //}
    }
}
